/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ProductCategory;

import Category.CategoryBean;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.List;
import java.util.Vector;

/**
 *
 * @author Mukesh
 */
public class Query {

    static Connection con = null;
    static ResultSet rs = null;
    
    public static int add(ProductCategoryBean cb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("insert into product_category values(PRODUCTCATEGORYID.nextval,?,sysdate)");
            pre.setString(1, cb.getProducCategoryName());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
    
    public static int edit(ProductCategoryBean cb) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("update product_category set PRODUCT_CATEGORY_NAME=?,ENTRY_DATE=sysdate where PRODUCT_CATEGORY_ID=?");
            pre.setString(1, cb.getProducCategoryName());
            pre.setInt(2, cb.getProducCategoryId());
            i = pre.executeUpdate();
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector viewAll() throws SQLException {
        int i = 0;
        Vector<ProductCategoryBean> list=new Vector<ProductCategoryBean>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from product_category");
            rs = pre.executeQuery();
            while (rs.next()) {
                ProductCategoryBean pb = new ProductCategoryBean();
                pb.setProducCategoryId(rs.getInt(1));
                pb.setProducCategoryName(rs.getString(2));
                pb.setEntryDate(rs.getDate(3));
                list.add(pb);
            }
        } catch (Exception e) {
            System.out.println(e);
            return list;
        } finally {
            con.close();
        }
        return list;
    }
    
    public static int delete(int id) throws SQLException
        {
            int i=0;
            try
            {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("delete from product_category where product_category_id=?");
              pre.setInt(1,id);
              i=pre.executeUpdate();
            }
            catch(Exception e)
            {
               return i; 
            }
            finally
            {
              con.close();  
            }
            return i;
        }
}
